package jpg1980.gttunes.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import jpg1980.gttunes.beans.Car;
import jpg1980.gttunes.beans.DriveTrain;
import jpg1980.gttunes.beans.Manufacturer;
import jpg1980.gttunes.beans.TuneBasic;
import jpg1980.gttunes.beans.TuneFilter;
import jpg1980.gttunes.beans.Tuner;
import jpg1980.gttunes.dao.AbstractDao;
import jpg1980.gttunes.dao.TuneBasicDao;

public class TuneBasicDaoMySqlImpl extends AbstractDao implements TuneBasicDao
{
	
	static Logger logger = Logger.getLogger(TuneBasicDaoMySqlImpl.class);
	
	public TuneBasicDaoMySqlImpl()
	{
		super();
	}
	
	@Override
	public int create(TuneBasic tuneBasic)
	{
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public void delete(int id)
	{
		// TODO Auto-generated method stub

	}

	@Override
	public TuneBasic read(int id)
	{
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<TuneBasic> readAll()
	{
		List<TuneBasic> tuneBasicList = new ArrayList<TuneBasic>();
		
		PreparedStatement readAllStatement;
		
		try
		{
			Connection conn = getConnection();

			logger.debug("");
			readAllStatement = conn.prepareStatement("SELECT " + "TUNE.tuneID, " + "CAR.manufacturerName, " + "CAR.carModelName, "
					+ "TUNE.performancePoints, " + "CAR.driveTrainName, " + "TUNE.tyresFront, " + "TUNE.tyresRear, " + "TUNER.tunerName, "
					+ "CASE WHEN AVG(USER_TUNE_RATING.rating) > 0 " + "THEN AVG(USER_TUNE_RATING.rating) " + "ELSE 'unrated' "
					+ "END As 'rating' " + "FROM 	" + "TUNE LEFT OUTER JOIN USER_TUNE_RATING ON TUNE.tuneID = USER_TUNE_RATING.tuneID,"
					+ "CAR, " + "TUNER " + "WHERE " + "TUNE.tunerName = TUNER.tunerName " + "AND TUNE.carModelName = CAR.carModelName "
					+ "GROUP BY " + "TUNE.tuneID");
			
			ResultSet rs = readAllStatement.executeQuery();
			
			while (rs.next())
			{
				Manufacturer manufacturer = new Manufacturer(rs.getString("manufacturerName"));
				
				DriveTrain driveTrain = new DriveTrain(rs.getString("driveTrainName"));
				
				Car car = new Car(rs.getString("carModelName"), driveTrain, manufacturer);
				
				Tuner tuner = new Tuner(null, rs.getString("tunerName"));
				
				float rating = 0.0f;
				if (rs.getString("rating").equalsIgnoreCase("unrated") == false)
				{
					rating = Float.parseFloat(rs.getString("rating"));
				}
				
				TuneBasic tuneBasic = new TuneBasic(	rs.getInt("tuneID"),
														car,
														rs.getInt("performancePoints"),
														rs.getString("tyresFront"),
														rs.getString("tyresRear"),
														tuner,
														rating);
				
				tuneBasicList.add(tuneBasic);
			}
		}
		catch (SQLException e)
		{
			super.cleanUp();
		}
		finally
		{
			super.cleanUp();
		}
		
		return tuneBasicList;
	}

	@Override
	public List<TuneBasic> readSelection(int[] ids)
	{
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void update(TuneBasic tuneBasic)
	{
		// TODO Auto-generated method stub

	}

	@Override
	public List<TuneBasic> readByTuneFilter(TuneFilter tuneFilter)
	{
		List<TuneBasic> tuneBasicList = new ArrayList<TuneBasic>();
		
		PreparedStatement readAllStatement;
		
		String sql = "SELECT TUNE.tuneID, CAR.manufacturerName, CAR.carModelName, TUNE.performancePoints, CAR.driveTrainName, TUNE.tyresFront, TUNE.tyresRear, TUNER.tunerName, "
		+ "CASE WHEN AVG(USER_TUNE_RATING.rating) > 0 " + "THEN AVG(USER_TUNE_RATING.rating) " + "ELSE 'unrated' "
		+ "END As 'rating' " + "FROM " + "TUNE LEFT OUTER JOIN USER_TUNE_RATING ON TUNE.tuneID = USER_TUNE_RATING.tuneID,"
		+ "CAR, " + "TUNER " + "WHERE " + "TUNE.tunerName = TUNER.tunerName " + "AND TUNE.carModelName = CAR.carModelName";
		
		if (StringUtils.isBlank(tuneFilter.getManufacturerName()) == false)
			sql += " AND CAR.manufacturerName = \"" + tuneFilter.getManufacturerName() + "\"";
		
		if (StringUtils.isBlank(tuneFilter.getCarModelName()) == false)
			sql += " AND CAR.carModelName = \"" + tuneFilter.getCarModelName() + "\"";
		
		if (StringUtils.isBlank(tuneFilter.getPpRange()) == false)
		{
			//sql += " AND CAR.manufacturerName = \"" tuneFilter.getManufacturerName();
		}
		
		if (StringUtils.isBlank(tuneFilter.getDriveTrainName()) == false)
			sql += " AND CAR.driveTrainName = \"" + tuneFilter.getDriveTrainName() + "\"";
		
		sql += " GROUP BY TUNE.tuneID";
		
		try
		{
			Connection conn = getConnection();

			logger.debug("");
			readAllStatement = conn.prepareStatement(sql);
			
			ResultSet rs = readAllStatement.executeQuery();
			
			while (rs.next())
			{
				Manufacturer manufacturer = new Manufacturer(rs.getString("manufacturerName"));
				
				DriveTrain driveTrain = new DriveTrain(rs.getString("driveTrainName"));
				
				Car car = new Car(rs.getString("carModelName"), driveTrain, manufacturer);
				
				Tuner tuner = new Tuner(null, rs.getString("tunerName"));
				
				float rating = 0.0f;
				if (rs.getString("rating").equalsIgnoreCase("unrated") == false)
				{
					rating = Float.parseFloat(rs.getString("rating"));
				}
				
				TuneBasic tuneBasic = new TuneBasic(	rs.getInt("tuneID"),
														car,
														rs.getInt("performancePoints"),
														rs.getString("tyresFront"),
														rs.getString("tyresRear"),
														tuner,
														rating);
				
				tuneBasicList.add(tuneBasic);
			}
		}
		catch (SQLException e)
		{
			super.cleanUp();
		}
		finally
		{
			super.cleanUp();
		}
		
		return tuneBasicList;
	}

}
